package edu.cs.usask.ca.silver.persistence.sql;

/**
 * Collection of SQL Strings for model project related activities. Acts on
 * ModelProject and ModelVersion tables.
 * 
 * @author Daniel Funk
 * @author Yudi Xue
 */
public interface SQLModelProjectStringsI {

	// When a new project is created mp_nextMvIdPerProject is initialised to 1.
	// mp_nextMvIdPerProject is no longer in use after applied with UUID
	public static final String MODEL_PROJECT_INSERT = "INSERT INTO MProject (mp_identity, mp_version, mp_update_time, mp_group_identity, mp_name, mp_creator, mp_type, mp_creationDate, mp_description)"
			+ "VALUES ('%s', '%s', CURRENT_TIMESTAMP, '%s', '%s', '%s', '%s', CURRENT_TIMESTAMP, '%s')";

	// copy to remote database
	public static final String MODEL_PROJECT_INSERT_TO_REMOTE = "INSERT INTO MProject (mp_identity, mp_version, mp_update_time, mp_group_identity, mp_name, mp_creator, mp_type, mp_creationDate, mp_description)"
			+ "VALUES ( (SELECT xpath('//mydefns:MP_IDENTITY/text()', '%s', ARRAY[ARRAY['mydefns', 'silver']]) "
			+ ",(SELECT xpath('//mydefns:MP_VERSION/text()', '%s', ARRAY[ARRAY['mydefns', 'silver']]),CURRENT_TIMESTAMP, '%s', (SELECT xpath('//mydefns:MP_NAME/text()', '%s', ARRAY[ARRAY['mydefns', 'silver']])),"
			+ "(SELECT xpath('//mydefns:MP_CREATOR/text()', '%s', ARRAY[ARRAY['mydefns', 'silver']])),"
			+ " (SELECT xpath('//mydefns:MP_TYPE/text()', '%s', ARRAY[ARRAY['mydefns', 'silver']])), CURRENT_TIMESTAMP, "
			+ "(SELECT xpath('//mydefns:MP_DESCRIPTION/text()', '%s', ARRAY[ARRAY['mydefns', 'silver']])))";

	public static final String MODEL_PROJECT_SELECT = "SELECT mp_identity, mp_version, mp_update_time, mp_name, mp_creator, mp_group_identity, mp_creationDate, mp_description "
			+ "FROM MProject WHERE mp_identity = '%s'";

	public static final String MODEL_PROJECT_SELECT_ANY = "SELECT * FROM MProject";
	public static final String MODEL_PROJECT_SELECT_ALLIDS = "SELECT mp_identity FROM MProject";
	
	public static final String MODEL_PROJECT_SELECT_VERSION_IDENTITY = "SELECT mp_version FROM MProject";
	public static final String MODEL_PROJECT_SELECT_UPDATE_TIME = "SELECT mp_update_time FROM MProject";
	
	public static final String MODEL_PROJECT_SELECT_NAME = "SELECT mp_name FROM MProject WHERE mp_identity = '%s'";
	public static final String MODEL_PROJECT_SELECT_TYPE = "SELECT mp_type FROM MProject WHERE mp_identity = '%s'";
	public static final String MODEL_PROJECT_SELECT_CREATOR = "SELECT mp_creator FROM MProject WHERE mp_identity = '%s'";
	public static final String MODEL_PROJECT_SELECT_CREATIONDATE = "SELECT mp_creationDate FROM MProject WHERE mp_identity = '%s'";
	public static final String MODEL_PROJECT_SELECT_DESCRIPTION = "SELECT mp_description FROM MProject WHERE mp_identity = '%s'";

	public static final String MODEL_PROJECT_SELECT_GROUP_ID = "SELECT mp_group_identity FROM MProject WHERE mp_identity = '%s'";

	public static final String MODEL_PROJECT_SELECT_NAME_BY_PARENT_GROUP_ID = "SELECT mp_name FROM MProject WHERE mp_group_identity = '%s'";

	public static final String MODEL_PROJECT_SELECT_MPROJECT_NAMES_WITHIN_SAME_GROUP = "SELECT mp_name FROM MProject WHERE mp_group_identity = (SELECT mp_group_identity FROM MProject WHERE mp_identity = '%s')";

	public static final String MODEL_PROJECT_SELECT_MPROJECT_IDS_AND_NAMES_WITHIN_SAME_GROUP = "SELECT mp_identity, mp_name FROM MProject WHERE mp_group_identity = (SELECT mp_group_identity FROM MProject WHERE mp_identity = '%s')";

	public static final String MODEL_PROJECT_SELECT_MVERSIONIDS = "SELECT mv_identity FROM MVersion WHERE mv_mp_identity = '%s'";
	//public static final String MODEL_PROJECT_SELECT_MVERSIONIDANDNAME = "SELECT mv_identity, mv_name FROM MVersion WHERE mv_mp_identity = '%s'";
	public static final String MODEL_PROJECT_SELECT_MVERSIONIDANDNAME = "SELECT mv_identity, mv_name, mv_update_time, mv_name, mv_type, mv_creator  FROM MVersion WHERE mv_mp_identity = '%s'";

	// DERBY does not support `LIMIT` -check
	// http://db.apache.org/derby/faq.html#limit
	// public static final String MODEL_PROJECT_SELECT_MOSTRECENTMVERSIONID =
	// "SELECT mv_ID FROM MVersion WHERE mv_mp_id = %d " +
	// "ORDER BY mv_creationDate DESC LIMIT 1";
	// public static final String MODEL_PROJECT_SELECT_MOSTRECENTMVERSIONID =
	// "SELECT mv_ID FROM (SELECT ROW_NUMBER() OVER() AS rownum, mv_ID FROM MVersion WHERE mv_mp_id = %d ORDER BY mv_creationDate DESC ) AS tmp MVersion WHERE rownum = 1";

	public static final String MODEL_PROJECT_SET_VERSION = "UPDATE MProject SET mp_version = '%s', mp_update_time = CURRENT_TIMESTAMP WHERE mp_identity = '%s'";
	
	public static final String MODEL_PROJECT_SET_NAME = "UPDATE MProject SET mp_name = '%s', mp_update_time = CURRENT_TIMESTAMP WHERE mp_identity = '%s'";
	public static final String MODEL_PROJECT_SET_DESCRIPTION = "UPDATE MProject SET mp_description = '%s', mp_update_time = CURRENT_TIMESTAMP WHERE mp_identity = '%s'";
	public static final String MODEL_PROJECT_SET_TYPE = "UPDATE MProject SET mp_type = '%s', mp_update_time = CURRENT_TIMESTAMP WHERE mp_identity = '%s'";

	public static final String MODEL_PROJECT_DELETE = "DELETE FROM MProject WHERE mp_identity = '%s'";

	public static final String MODEL_PROJECT_SELECT_ASSOCIATEDFILEIDS = "SELECT mp2f_f_identity FROM MP2F WHERE mp2f_mp_identity = '%s'";

}
